﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Controls;
using Billing.Controls;
using Billing.Dtos;

namespace Billing.Utilities
{
    public static class Database
    {
        static public int BranchSelected;

        //static SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BillingConnectionString"].ConnectionString);
        static SqlConnection connection = null;
        static SqlConnection salariesConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SalariesConnectionString"].ConnectionString);
        static SqlConnection connectionMain = new SqlConnection(ConfigurationManager.ConnectionStrings["MainBillingConnectionString"].ConnectionString);
        static SqlConnection connectionFinancial = new SqlConnection(ConfigurationManager.ConnectionStrings["FinancialConnectionString"].ConnectionString);
        static SqlConnection connectionManufacturing = new SqlConnection(ConfigurationManager.ConnectionStrings["ManufacturingConnectionString"].ConnectionString);

        static string conStringChandanagar = ConfigurationManager.ConnectionStrings["ChandanagarConnectionString"].ToString();
        static string conStringMiyapur = ConfigurationManager.ConnectionStrings["MiyapurConnectionString"].ToString();
        static string conStringKukatpally = ConfigurationManager.ConnectionStrings["KukatpallyConnectionString"].ToString();
        static string conStringAmeerpet = ConfigurationManager.ConnectionStrings["AmeerpetConnectionString"].ToString();
        static string conStringJNTU = ConfigurationManager.ConnectionStrings["JNTUConnectionString"].ToString();

        //static string Branch = ConfigurationManager.AppSettings["Branch"].ToString();
        //static string BranchLetter = ConfigurationManager.AppSettings["BranchLetter"].ToString();
        //static string BranchCakeLetter = ConfigurationManager.AppSettings["BranchCakeLetter"].ToString();
        public static string Branch = string.Empty;
        public static string BranchLetter = string.Empty;
        public static string BranchCakeLetter = string.Empty;

        internal static DataTable GetAllBillingItems()
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ItemCode, ItemName, CostPrice, SellingPrice, D.DeptName AS Dept, AcceptNegativeStock, AcceptDecimalStock, PopupLimit, IsLessStockShown, S.StockAvailable, GroupID, D.IsSlipRequired, VAT FROM MasterItems MI LEFT OUTER JOIN Stock S ON MI.StockID = S.StockID JOIN Department D ON MI.Dept = D.DeptID ORDER BY CONVERT(BIGINT, ItemCode)", connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            finally
            {
                connection.Close();
            }
            return null;
        }

        internal static int AddStock(int itemCode, double stock)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddStockByItemCode {0}, {1}, '{2}'", itemCode, stock, Storage.LoginUser), connection, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return int.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static int AddStockToBranch(int itemCode, double stock, string branch)
        {
            SqlConnection con = null;
            SqlTransaction trans = null;
            SqlTransaction transLocal = null;
            int result = 0;
            try
            {
                if (branch == "1")
                    con = new SqlConnection(conStringChandanagar);
                else if (branch == "2")
                    con = new SqlConnection(conStringMiyapur);
                else if (branch == "3")
                    con = new SqlConnection(conStringKukatpally);
                else if (branch == "4")
                    con = new SqlConnection(conStringAmeerpet);
                else if (branch == "5")
                    con = new SqlConnection(conStringJNTU);

                if (con.State != ConnectionState.Open)
                    con.Open();

                trans = con.BeginTransaction();

                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddStockToBranch {0}, {1}, {2}, '{3}'", itemCode, stock, Branch, Storage.LoginUser), con, trans))
                {
                    result = cmd.ExecuteNonQuery();
                    if (result > 0)
                        trans.Commit();
                }

                //Decrease in Current Branch
                try
                {
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    transLocal = connection.BeginTransaction();
                    using (SqlCommand cmd = new SqlCommand(string.Format("exec ReduceStockFromBranch {0}, {1}, {2}, '{3}'", itemCode, stock, branch, Storage.LoginUser), connection, transLocal))
                    {
                        result = cmd.ExecuteNonQuery();
                        if (result > 0)
                        {
                            transLocal.Commit();
                            return result;
                        }
                    }
                }
                catch (Exception) { }
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                //connection.Close();
                //connection.Dispose();
                transLocal.Dispose();
                con.Close();
                con.Dispose();
                connection.Close();
                trans.Dispose();
            }
        }

        internal static int AddDamage(int itemCode, double stock)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddDamageByItemCode {0}, {1}, {2}", itemCode, stock, Storage.LoginUser), connection, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return int.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static void AddLoginInfo(string userName)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddLoginInfo '{0}'", userName), connection))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            { }
            finally
            {
                connection.Close();
            }
        }

        internal static string ValidateUser(string userID, string password)
        {
            try
            {
                switch (BranchSelected)
                {
                    case 1:
                        connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ChandanagarConnectionString"].ConnectionString);
                        Branch = BranchSelected.ToString();
                        BranchLetter = "C";
                        BranchCakeLetter = "CB";
                        break;
                    case 2:
                        connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MiyapurConnectionString"].ConnectionString);
                        Branch = BranchSelected.ToString();
                        BranchLetter = "M";
                        BranchCakeLetter = "MB";
                        break;
                    case 3:
                        connection = new SqlConnection(ConfigurationManager.ConnectionStrings["KukatpallyConnectionString"].ConnectionString);
                        Branch = BranchSelected.ToString();
                        BranchLetter = "K";
                        BranchCakeLetter = "KB";
                        break;
                    case 4:
                        connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AmeerpetConnectionString"].ConnectionString);
                        Branch = BranchSelected.ToString();
                        BranchLetter = "A";
                        BranchCakeLetter = "AB";
                        break;
                    case 5:
                        connection = new SqlConnection(ConfigurationManager.ConnectionStrings["JNTUConnectionString"].ConnectionString);
                        Branch = BranchSelected.ToString();
                        BranchLetter = "J";
                        BranchCakeLetter = "JB";
                        break;
                    default:
                        connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BillingConnectionString"].ConnectionString);
                        Branch = ConfigurationManager.AppSettings["Branch"].ToString();
                        BranchLetter = ConfigurationManager.AppSettings["BranchLetter"].ToString();
                        BranchCakeLetter = ConfigurationManager.AppSettings["BranchCakeLetter"].ToString();
                        break;
                }

                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT EmpID FROM Login WHERE UserName = '{0}' AND Password = '{1}'", userID, password), connection))
                {
                    DataTable ds = new DataTable();
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                    {
                        AddLoginInfo(ds.Rows[0][0].ToString());
                        return ds.Rows[0][0].ToString();
                    }
                }
                return string.Empty;
            }
            catch (Exception)
            {
                return string.Empty;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }

        internal static string GenerateBill(ItemCollection items, string billNo, double discountInPer, double discountInRs, double totalAmt, double totalNetAmt, double cashPayment, double sodexHoPayment, double cardPayment, string discountReason)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            string res = string.Empty;
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddBillItem '{0}', {1}, {2}, {3}, {4}, {5}, {6}, {7}, '{8}', '{9}'", BranchLetter, discountInPer, discountInRs, totalAmt, totalNetAmt, cashPayment, sodexHoPayment, cardPayment, discountReason, Storage.LoginUser)))
                {
                    cmd.Connection = connection;
                    cmd.Transaction = trans;

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable ds = new DataTable();
                        adapter.Fill(ds);
                        if (!(ds != null && ds.Rows.Count >= 0))
                            throw new Exception(string.Empty);
                        res = ds.Rows[0][0].ToString();
                    }

                    foreach (BillingGridItemControl item in items)
                    {
                        if (item.txtCode.Text != string.Empty)
                        {
                            cmd.CommandText = string.Format("exec AddBillDetailItem '{0}', {1}, {2}", res, item.txtCode.Text, item.txtQty.Text);
                            int result = cmd.ExecuteNonQuery();
                            if (result <= 0)
                                throw new Exception(string.Empty);
                        }
                    }
                }

                trans.Commit();
                return res;
            }
            catch (Exception)
            {
                trans.Rollback();
                return string.Empty;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static int GetNextBillNo()
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand("exec GetTokenBillNo", connection))
                {
                    return int.Parse(cmd.ExecuteScalar().ToString());
                }
            }
            catch (Exception)
            {
                return 0;
            }
            finally
            {
                connection.Close();
            }
        }

        //internal static int GetNextBillNo()
        //{
        //    try
        //    {
        //        DataTable ds = new DataTable();
        //        string query = string.Empty;

        //        using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT Top 1 * FROM Bill WHERE DATEDIFF(DD, BillDate, GETDATE()) = 0 ORDER BY ID DESC", connection))
        //        {
        //            adapter.Fill(ds);
        //            bool isIncremented = false;
        //            if (ds != null && ds != null && ds.Rows.Count > 0)
        //            {
        //                query = "SELECT IDENT_CURRENT('Bill') + IDENT_INCR('Bill') AS BillNo";
        //                isIncremented = true;
        //            }
        //            else
        //            {
        //                query = "SELECT IDENT_CURRENT('Bill') AS BillNo";
        //            }
        //            ds = new DataTable();
        //            using (SqlDataAdapter adapterQuery = new SqlDataAdapter(query, connection))
        //            {
        //                adapterQuery.Fill(ds);
        //                if (ds != null && ds.Rows.Count > 0)
        //                {
        //                    int val = int.Parse(ds.Rows[0][0].ToString());
        //                    if (val > 1 && !isIncremented)
        //                        val = val + 1;
        //                    return val;
        //                }
        //                return 0;
        //            }
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return 0;
        //    }
        //}

        //internal static int AddCreditNote(CreditNoteDto creditNote)
        //{
        //    if (connection.State != ConnectionState.Open)
        //        connection.Open();
        //    SqlTransaction trans = connection.BeginTransaction();
        //    try
        //    {
        //        using (SqlCommand cmd = new SqlCommand(string.Format("exec AddCreditNote {0}, {1}, {2}, {3}", creditNote.CompanyID, creditNote.NetTotalAmount, creditNote.BillNumber, Branch), connection, trans))
        //        {
        //            string result = cmd.ExecuteNonQuery().ToString();
        //            if (result != null && result != string.Empty)
        //            {
        //                trans.Commit();
        //                return Int32.Parse(result);
        //            }
        //        }
        //        trans.Commit();
        //        return 0;
        //    }
        //    catch (Exception)
        //    {
        //        trans.Rollback();
        //        return -1;
        //    }
        //    finally
        //    {
        //        connection.Close();
        //        trans.Dispose();
        //    }
        //}

        internal static int AddExpense(ExpenseDto expense)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddExpense {0}, {1}, '{2}', {3}, '{4}'", expense.ExpenseID, expense.Amount, expense.Description, Branch, Storage.LoginUser), connection, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return int.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static DataTable GetCreditNoteCompanies()
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ID, CreditNoteCompany FROM MasterCreditNotes", connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static List<Expense> GetExpenseDetails()
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ID, ExpenseItem FROM MasterExpenses ORDER BY ExpenseItem", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null)
                    {
                        List<Expense> expenses = new List<Expense>();
                        foreach (DataRow row in ds.Rows)
                            expenses.Add(new Expense { ID = int.Parse(row[0].ToString()), ExpenseItem = row[1].ToString() });
                        return expenses;
                    }
                }
            }
            catch
            { }
            return null;
        }

        internal static DataTable GetAllExpensesForToday()
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ME.ExpenseItem, E.Amount, E.Description, E.Cashier FROM Expense E JOIN MasterExpenses ME ON E.Expense = ME.ID WHERE DATEDIFF(day, GETDATE(), DATE) = 0 ORDER BY E.ID ", connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        //DataRemainderPopup
        internal static string GetExpenseTotalForToday()
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT SUM(Amount) FROM Expense WHERE DATEDIFF(day, GETDATE(), DATE) = 0 AND Cashier = '{0}'", Storage.LoginUser)))
                {
                    cmd.Connection = connection;
                    return cmd.ExecuteScalar().ToString();
                }
            }
            catch (Exception)
            {
            }
            finally
            {
                connection.Close();
            }
            return "0";
        }

        internal static int CancelBill(string billNo, string reason)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec CancelBill '{0}', '{1}'", billNo, reason), connection, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return int.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static DataTable GetDepartments()
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT DeptID, DeptCode + '-' + DeptName AS DeptName FROM Department", connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static DataTable GetItem(string itemCode)
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ID, ItemCode, ItemName, CostPrice, SellingPrice, Dept, AcceptNegativeStock, AcceptDecimalStock, PopupLimit, IsLessStockShown, StockAvailable, GroupID FROM MasterItems MI JOIN Stock S ON MI.StockID = S.StockID WHERE ItemCode = " + itemCode, connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static List<Branch> GetBranches()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT BranchID, BranchName FROM MasterBranches", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null)
                    {
                        List<Branch> branchList = new List<Branch>();
                        foreach (DataRow row in ds.Rows)
                            branchList.Add(new Branch { BranchID = int.Parse(row[0].ToString()), BranchName = row[1].ToString() });
                        return branchList;
                    }
                }
            }
            catch
            { }
            return null;
        }

        //Connect to Salaries Database
        /*internal static List<Employee> GetEmployeesByBranch(string branch)
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT E.EmpID, E.EmpName, ED.Department, EDe.Designation, B.Branch FROM Employees E JOIN Departments ED ON E.Department = ED.DeptID JOIN Designations EDe ON E.Designation = EDe.DesignationID JOIN Branches B ON B.BranchID = E.Branch WHERE E.Branch = {0}", branch), salariesConnection))
                {
                    //WHERE E.Branch = " + Branch.ToString()
                    adapter.Fill(ds);
                    if (ds != null)
                    {
                        List<Employee> empList = new List<Employee>();
                        foreach (DataRow row in ds.Rows)
                            empList.Add(new Employee { EmpID = int.Parse(row[0].ToString()), EmpName = row[1].ToString(), Department = row[2].ToString(), Designation = row[3].ToString(), Branch = row[4].ToString() });
                        return empList;
                    }
                }
            }
            catch
            { }
            return null;
        }*/

        //Connect to Local Database
        internal static List<Employee> GetEmployeesByBranch(string branch)
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT E.EmpID, E.EmpName, ED.Department, EDe.Designation, B.BranchName, CAST(E.EmpID AS NVARCHAR(5)) + ' - ' + E.EmpName AS EmpIDName FROM Employees E JOIN Departments ED ON E.Department = ED.DeptID JOIN Designations EDe ON E.Designation = EDe.DesignationID JOIN MasterBranches B ON B.BranchID = E.Branch WHERE E.Branch = {0}", branch), connection))
                {
                    adapter.Fill(ds);
                    if (ds != null)
                    {
                        List<Employee> empList = new List<Employee>();
                        foreach (DataRow row in ds.Rows)
                            empList.Add(new Employee { EmpID = int.Parse(row[0].ToString()), EmpName = row[1].ToString(), Department = row[2].ToString(), Designation = row[3].ToString(), Branch = row[4].ToString(), EmpIDName = row[5].ToString() });
                        return empList;
                    }
                }
            }
            catch
            { }
            return null;
        }

        //Connect to Salaries Database
        /*internal static int AddLeaveOrOT(string empID, LeaveOrOT type, string dt, bool isHalfDay)
        {
            if (salariesConnection.State != ConnectionState.Open)
                salariesConnection.Open();
            SqlTransaction trans = salariesConnection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddLeaveOrOTFromBranch {0}, {1}, '{2}', {3}, {4}", empID, (int)type, dt, isHalfDay, Branch), salariesConnection, trans))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable ds = new DataTable();
                        adapter.Fill(ds);
                        trans.Commit();
                        return Convert.ToInt32(ds.Rows[0][0].ToString());
                    }
                }
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                salariesConnection.Close();
            }
        }*/

        //Connect to Local Database
        internal static int AddLeaveOrOT(string empID, LeaveOrOT type, string dt, bool isHalfDay)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddLeaveOrOTFromBranch {0}, {1}, '{2}', {3}, {4}", empID, (int)type, dt, isHalfDay, Branch), connection, trans))
                {
                    cmd.CommandType = CommandType.Text;
                    int result = cmd.ExecuteNonQuery();
                    trans.Commit();
                    return result;
                    //using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    //{
                    //    DataTable ds = new DataTable();
                    //    adapter.Fill(ds);
                    //    trans.Commit();
                    //    return Convert.ToInt32(ds.Rows[0][0].ToString());
                    //}
                }
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        //Type
        //Advance - 1
        //Bills - 2
        //Connect to Salaries Database
        /*internal static DataTable GetDebitTransactionsAddedToday(int type)
        {
            try
            {
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT E.EmpName, Amount, Date FROM DebitTransactions D JOIN Employees E ON D.EmpID = E.EmpID WHERE TransactionType = {0} AND DATEDIFF(day, GETDATE(), D.AddedDate) = 0 AND FromBranch = {1} ORDER BY AddedDate", type.ToString(), Branch), salariesConnection);
                adapter.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
            }
            catch
            { }
            return null;
        }*/

        //Connect to Local Database
        internal static DataTable GetDebitTransactionsAddedToday(int type)
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT E.EmpName, Amount, Date FROM DebitTransactions D JOIN Employees E ON D.EmpID = E.EmpID WHERE TransactionType = {0} AND DATEDIFF(day, GETDATE(), D.AddedDate) = 0 AND FromBranch = {1} ORDER BY AddedDate", type.ToString(), Branch), connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        //DataRemainderPopup
        internal static string GetTotalDebitTransactionsAddedToday(int type)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();

            try
            {
                DataTable ds = new DataTable();
                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT SUM(Amount) FROM DebitTransactions D JOIN Employees E ON D.EmpID = E.EmpID WHERE TransactionType = {0} AND DATEDIFF(day, GETDATE(), D.AddedDate) = 0 AND FromBranch = {1} AND Cashier = {2}", type.ToString(), Branch, Storage.LoginUser)))
                {
                    cmd.Connection = connection;
                    return cmd.ExecuteScalar().ToString();
                }
            }
            catch
            { }
            finally 
            {
                connection.Close();
            }
            return "0";
        }

        //Type
        //Leaves - 1
        //OT - 2
        //Connect to Salaries Database
        /*internal static DataTable GetCreditTransactionsAddedToday(int type)
        {
            try
            {
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT E.EmpName, CT.Date, IsHalfDay FROM CreditTransactions CT JOIN Employees E ON E.EmpID = CT.EmpID WHERE TransactionType = {0} AND DATEDIFF(day, GETDATE(), AddedDate) = 0 AND FromBranch = {1} ORDER BY AddedDate", type.ToString(), Branch), salariesConnection);
                adapter.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
            }
            catch
            { }
            return null;
        }*/

        //Connect to Local Database
        internal static DataTable GetCreditTransactionsAddedToday(int type)
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT E.EmpName, CT.Date, IsHalfDay FROM CreditTransactions CT JOIN Employees E ON E.EmpID = CT.EmpID WHERE TransactionType = {0} AND DATEDIFF(day, GETDATE(), AddedDate) = 0 AND FromBranch = {1} ORDER BY AddedDate", type.ToString(), Branch), connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        //Connect to Salaries Database
        /*internal static int AddAdvanceOrBill(string empID, string amount, AdvanceOrBill type, string dt)
        {
            if (salariesConnection.State != ConnectionState.Open)
                salariesConnection.Open();
            SqlTransaction trans = salariesConnection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec PayAdvanceOrBillFromBranch {0}, {1}, {2}, '{3}', {4}", empID, amount, (int)type, dt, Branch), salariesConnection, trans))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        trans.Commit();
                        return Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
                    }
                }
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                salariesConnection.Close();
            }
        }*/

        //Connect to Local Database
        internal static int AddAdvanceOrBill(string empID, string amount, AdvanceOrBill type, string dt)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec PayAdvanceOrBillFromBranch {0}, {1}, {2}, '{3}', {4}, '{5}'", empID, amount, (int)type, dt, Branch, Storage.LoginUser), connection, trans))
                {
                    cmd.CommandType = CommandType.Text;
                    int result = cmd.ExecuteNonQuery();
                    trans.Commit();
                    return result;
                    //using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    //{
                    //    DataSet ds = new DataSet();
                    //    adapter.Fill(ds);
                    //    trans.Commit();
                    //    return Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
                    //}
                }
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static DataSet GetCompleteBillByBillNo(string billNo)
        {
            try
            {
                DataSet ds = new DataSet();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT BillNo, DiscountByPercentage, DiscountByCost, TotalNetAmount, CONVERT(VARCHAR, BillDate, 5) AS BillDate, CONVERT(VARCHAR(5), BillDate, 108) + ' ' + SUBSTRING(CONVERT(VARCHAR(19), BillDate, 100),18,2) AS BillTime,  UserName FROM Bill B JOIN Login L ON B.[User] = L.EmpID WHERE BillNo = '{0}' AND IsBillCancelled = 0 AND IsSpecialBill = 0;SELECT Item, ItemName, Qty, SellingPrice AS Price, SellingPrice * Qty AS Amount, AcceptDecimalStock FROM BillDetail BD JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE BillNo = '{0}'", billNo), connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static DataTable GetBillByBillNo(string billNo)
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT BillNo, TotalNetAmount AS Amount FROM Bill WHERE BillNo = '{0}' AND IsBillCancelled = 0", billNo), connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static int GetNextCakeBillNo()
        {
            DataTable ds = new DataTable();
            string query = string.Empty;

            using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Cake", connection))
            {
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    query = "SELECT IDENT_CURRENT('Cake') + IDENT_INCR('Cake') AS CakeBillNo";
                else
                    query = "SELECT IDENT_CURRENT('Cake') AS CakeBillNo";
                ds = new DataTable();
                using (SqlDataAdapter adapterQuery = new SqlDataAdapter(query, connection))
                {
                    adapterQuery.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                        return int.Parse(ds.Rows[0][0].ToString());
                }
            }
            return 0;
        }

        internal static List<CakeShape> GetCakeShapes()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ID, CakeShape FROM CakeShapes", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null)
                    {
                        List<CakeShape> cakeShapeList = new List<CakeShape>();
                        foreach (DataRow row in ds.Rows)
                            cakeShapeList.Add(new CakeShape { ID = int.Parse(row[0].ToString()), Shape = row[1].ToString() });
                        return cakeShapeList;
                    }
                }
            }
            catch
            { }
            return null;
        }

        internal static DataTable GetCakeModels()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ModelID, ModelDesc FROM CakeModels", connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static List<CakeFlavour> GetCakeFlavours()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ID, Flavour, PricePerKG FROM CakeFlavours", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null)
                    {
                        List<CakeFlavour> cakeFlavoursList = new List<CakeFlavour>();
                        foreach (DataRow row in ds.Rows)
                            cakeFlavoursList.Add(new CakeFlavour { FlavourID = int.Parse(row[0].ToString()), Flavour = row[1].ToString(), PricePerKG = double.Parse(row[2].ToString()) });
                        return cakeFlavoursList;
                    }
                }
            }
            catch
            { }
            return null;
        }

        internal static int AddNewCake(string cakeNo, string customerName, string phoneNo, string deliveryDate, string deliveryTime, string shape, string albumModelDesc, string desc, string message, int flavour, double qty, double totalAmtwithoutExtra, double extra, double extraValue, double advance, double balance, double netAmount, double discountPercentage, double discountAmount, bool isCakeDeliverd, string discountReason, string photo, string billNo)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddCake '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, '{21}', '{22}', '{23}', '{24}'", cakeNo, customerName, phoneNo, deliveryDate, deliveryTime, shape, albumModelDesc, desc, message, flavour, qty, totalAmtwithoutExtra, extra, extraValue, advance, balance, netAmount, discountPercentage, discountAmount, isCakeDeliverd, Branch, discountReason, Storage.LoginUser, photo, billNo), connection, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return Int32.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static int UpdateCake(string cakeNo, string customerName, string phoneNo, string deliveryDate, string deliveryTime, string shape, string albumModelDesc, string desc, string message, int flavour, double qty, double totalAmtwithoutExtra, double extra, double extraValue, double advance, double balance, double netAmount, double discountPercentage, double discountAmount, bool isCakeDeliverd, string discountReason, string photo, string billNo)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();

            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddCake '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, '{21}', '{22}', '{23}', '{24}'", cakeNo, customerName, phoneNo, deliveryDate, deliveryTime, shape, albumModelDesc, desc, message, flavour, qty, totalAmtwithoutExtra, extra, extraValue, advance, balance, netAmount, discountPercentage, discountAmount, isCakeDeliverd, Branch, discountReason, Storage.LoginUser, photo, billNo), connection, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return Int32.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static DataTable GetCakeDetails(string cakeNo)
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT C.CakeNo, C.CustomerName, C.PhoneNo, C.DeliveryDate, C.DeliveryTime, C.Shape AS ShapeID, CS.CakeShape, C.AlbumModelDesc, C.[Desc], C.Message, C.Flavour AS FlavourID, CF.Flavour, C.Qty, C.TotalwithoutExtra, C.Advance, C.Balance, C.NetAmount, C.ExtraPercentage, C.DiscountInPercentage, C.DiscountByCost, C.IsCakeDelivered, C.BookedDate, C.ExtraValue, C.DeliveredDate, C.IsChefCopyPrinted, C.PhotoSheet, C.BillNo, C.CashierDelivered FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.CakeNo = '{0}' AND C.IsCancelled = 0", cakeNo), connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static int PayBalanceAndUpdateCakeDelivery(string cakeNo, DateTime dt)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec PayBalanceAndUpdateCakeDelivery '{0}', '{1}'", cakeNo, dt.ToString("yyyy-MM-dd")), connection, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return int.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static int UnDeliverCake(string cakeNo)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec UnDeliverCake '{0}'", cakeNo), connection, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return int.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static int SaveAmount(DateTime dt, string amountType, string amount)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddAmount '{0}', {1} , {2}, '{3}'", dt.ToString("yyyy-MM-dd"), amountType, amount, Storage.LoginUser), connection, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return int.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static DataTable GetAmounts()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT SodexhoAmount, CreditCardAmount FROM Amounts WHERE DATEDIFF(day, GETDATE(), Date) = 0 AND Cashier = '{0}'", Storage.LoginUser), connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static DataTable GetItemDetailsForCreditNoteCompany(int companyID, string itemCode)
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("exec GetCreditNoteCompanyItemDetails {0}, {1}", companyID, itemCode), connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static string GenerateBillForCreditNoteCompany(ItemCollection items, double totalAmt)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            string res = string.Empty;
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddBillItemForCreditNote {0}, {1}", totalAmt, Storage.LoginUser)))
                {
                    cmd.Connection = connection;
                    cmd.Transaction = trans;

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable ds = new DataTable();
                        adapter.Fill(ds);

                        if (!(ds.Rows.Count >= 0))
                            throw new Exception();
                        res = ds.Rows[0][0].ToString();
                    }

                    foreach (CreditNoteBillingGridItemControl item in items)
                    {
                        if (item.txtCode.Text != string.Empty)
                        {
                            cmd.CommandText = string.Format("exec AddBillDetailItem '{0}', {1}, {2}", res, item.txtCode.Text, item.txtQty.Text);
                            int result = cmd.ExecuteNonQuery();
                            if (result <= 0)
                                throw new Exception();
                        }
                    }
                }

                trans.Commit();
                return res;
            }
            catch (Exception)
            {
                trans.Rollback();
                return string.Empty;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        //Updated
        internal static string GenerateBillForCreditNoteCompany(ItemCollection items, double totalAmt, CreditNoteDto creditNote)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            string res = string.Empty;
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddBillItemForCreditNote {0}, {1}, '{2}'", totalAmt, Storage.LoginUser, BranchLetter)))
                {
                    cmd.Connection = connection;
                    cmd.Transaction = trans;

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable ds = new DataTable();
                        adapter.Fill(ds);

                        if (!(ds.Rows.Count >= 0))
                            throw new Exception();
                        res = ds.Rows[0][0].ToString();
                    }

                    foreach (CreditNoteBillingGridItemControl item in items)
                    {
                        if (item.txtCode.Text != string.Empty)
                        {
                            cmd.CommandText = string.Format("exec AddBillDetailItem '{0}', {1}, {2}", res, item.txtCode.Text, item.txtQty.Text);
                            int result = cmd.ExecuteNonQuery();
                            if (result <= 0)
                                throw new Exception();
                        }
                    }

                    cmd.CommandText = string.Format("exec AddCreditNote {0}, {1}, {2}, {3}", creditNote.CompanyID, creditNote.NetTotalAmount, res, Branch);
                    cmd.ExecuteNonQuery();
                }

                trans.Commit();
                return res;
            }
            catch (Exception)
            {
                trans.Rollback();
                return string.Empty;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static DataTable GetAllCakesByDeliveryDate(DateTime dateTime)
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT CakeNo, CustomerName, PhoneNo, DeliveryDate, DeliveryTime, CS.CakeShape, C.AlbumModelDesc, C.[Desc], C.Message, CF.Flavour, C.Qty, C.NetAmount, C.IsCakeDelivered, C.Advance, C.Balance, C.ExtraPercentage, C.ExtraValue, C.DiscountByCost, C.BookedDate, C.IsChefCopyPrinted, C.PhotoSheet, C.BillNo FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE DATEDIFF(day, '{0}', C.DeliveryDate) = 0 AND C.IsCancelled = 0 ORDER BY BookedDate", dateTime.ToString("MM-dd-yyyy")), connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static DataTable GetAllCakesByPhoneNo(string phoneNo)
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT CakeNo, CustomerName, PhoneNo, DeliveryDate, DeliveryTime, CS.CakeShape, C.AlbumModelDesc, C.[Desc], C.Message, CF.Flavour, C.Qty, C.NetAmount, C.IsCakeDelivered, C.Advance, C.Balance, C.ExtraPercentage, C.ExtraValue, C.DiscountByCost, C.BookedDate FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE PhoneNo like '%{0}%' AND C.IsCancelled = 0 ORDER BY C.ID DESC", phoneNo), connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static int CancelCake(string cakeBillNo)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec CancelCake '{0}'", cakeBillNo), connection, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return int.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static DataTable GetItemDetailsForBulkOrder(string itemCode)
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("exec GetBulkOrderItemDetails {0}", itemCode), connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        //Last Parameter Type
        //1 - Non Box
        //2 - Box
        internal static string GenerateBillForBulkOrder(ItemCollection items, double totalAmt, string phoneNo, DateTime deliveryDate, string description, string advance, string balance, string discountByCost, string discountByPercentage, string discountReason, string customerName, string deliveryTime, string deliveryPlace, int type, double eachBoxCost, int boxQty)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            string res = string.Empty;
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddBillItemForBulkOrder {0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}'", totalAmt, phoneNo, deliveryDate.ToString("MM/dd/yyyy"), description, Storage.LoginUser, advance, balance, discountByCost, discountByPercentage, discountReason, BranchCakeLetter, customerName, deliveryTime, deliveryPlace, eachBoxCost.ToString(), boxQty.ToString(), Storage.LoginUser)))
                {
                    cmd.Connection = connection;
                    cmd.Transaction = trans;

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);

                        if (!(ds.Tables.Count >= 0 && ds.Tables[0].Rows.Count >= 0))
                            throw new Exception();
                        res = ds.Tables[0].Rows[0][0].ToString();
                    }

                    if (type == 1)
                    {
                        foreach (BulkOrderBillingGridItemControl item in items)
                        {
                            if (item.txtCode.Text != string.Empty)
                            {
                                //Stock Own't Reduce Here
                                cmd.CommandText = string.Format("exec AddBillDetailItemNoStockReduce '{0}', {1}, {2}", res, item.txtCode.Text, item.txtQty.Text);
                                int result = cmd.ExecuteNonQuery();
                                if (result <= 0)
                                    throw new Exception();
                            }
                        }
                    }
                    else if (type == 2)
                    {
                        foreach (BulkOrderBoxBillingGridItemControl item in items)
                        {
                            if (item.txtCode.Text != string.Empty)
                            {
                                //Stock Own't Reduce Here
                                cmd.CommandText = string.Format("exec AddBillDetailItemNoStockReduce '{0}', {1}, {2}", res, item.txtCode.Text, item.txtQty.Text);
                                int result = cmd.ExecuteNonQuery();
                                if (result <= 0)
                                    throw new Exception();
                            }
                        }
                    }
                }

                trans.Commit();
                return res;
            }
            catch (Exception)
            {
                trans.Rollback();
                return string.Empty;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static DataSet GetBulkOrderDetails(string billNo)
        {
            try
            {
                DataSet ds = new DataSet();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT TotalAmount, PhoneNo, CONVERT(VARCHAR, DeliveryDate, 106) AS DeliveryDate, Description, IsOrderCancelled, CancelledReason, IsOrderDelivered, BookedDate, [USER], Advance, Balance, DiscountByCost, DiscountByPercentage, DiscountReason, CustomerName, DeliveryTime, DeliveryPlace, EachBoxCost, Qty FROM BulkOrder WHERE BillNo = '{0}';SELECT MI.ItemCode, MI.ItemName, BD.Qty, MI.SellingPrice AS Rate, (MI.SellingPrice * BD.Qty) AS Amount FROM BillDetail BD JOIN MasterItems MI ON BD.Item = MI.ItemCode WHERE BillNo = '{0}'", billNo), connection))
                {
                    adapter.Fill(ds);
                    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static int DeliverBulkOrder(string bulkBillNo, DateTime deliveryDate)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec DeliverBulkOrder '{0}', '{1}', '{2}'", bulkBillNo, deliveryDate.ToString("MM/dd/yyyy"), Storage.LoginUser), connection, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return Int32.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static int UnDeliverBulkOrder(string bulkBillNo, DateTime deliveryDate)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec UnDeliverBulkOrder '{0}', '{1}'", bulkBillNo, deliveryDate.ToString("MM/dd/yyyy")), connection, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return int.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static int CancelBulkOrder(string bulkBillNo, string cancelReason)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec CancelBulkOrder '{0}', '{1}'", bulkBillNo, cancelReason), connection, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return int.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        //Returns true only if there are any bills available
        internal static bool CheckBillsAvailability()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT COUNT(*) FROM Bill", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                    {
                        int cnt = 0;
                        int.TryParse(ds.Rows[0][0].ToString(), out cnt);
                        if (cnt > 0)
                            return true;
                    }
                }
            }
            catch
            { }
            return false;
        }

        //Returns true only if there are any billdetails available
        internal static bool CheckBillDetailsAvailability()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT COUNT(*) FROM BillDetail", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                    {
                        int cnt = 0;
                        int.TryParse(ds.Rows[0][0].ToString(), out cnt);
                        if (cnt > 0)
                            return true;
                    }
                }
            }
            catch
            { }
            return false;
        }

        //Returns true only if there are cake bills available from past 5 days.
        internal static bool CheckCakeBillsAvailability()
        {
            DataTable ds = new DataTable();

            try
            {
                //SqlDataAdapter adapter = new SqlDataAdapter("SELECT COUNT(*) FROM Cake WHERE DATEDIFF(day, GETDATE(), BookedDate) = 0", connection);
                //SqlDataAdapter adapter = new SqlDataAdapter("SELECT COUNT(*) FROM [115.252.109.175].[Billing].[dbo].[Cake]", connection);
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT COUNT(*) FROM Cake WHERE DATEDIFF(day, dateadd(day,datediff(day,0,getdate())-5,0), BookedDate) > 0", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                    {
                        int cnt = 0;
                        int.TryParse(ds.Rows[0][0].ToString(), out cnt);
                        if (cnt > 0)
                            return true;
                    }
                }
            }
            catch
            { }

            try
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT COUNT(*) FROM CakeBalance", connection))
                {
                    ds = new DataTable();
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                    {
                        int cnt = 0;
                        int.TryParse(ds.Rows[0][0].ToString(), out cnt);
                        if (cnt > 0)
                            return true;
                    }
                }
            }
            catch { }

            return false;
        }

        //Returns true only if there are any Credit Notes available
        internal static bool CheckCreditNotesAvailability()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT COUNT(*) FROM CreditNote", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                    {
                        int cnt = 0;
                        int.TryParse(ds.Rows[0][0].ToString(), out cnt);
                        if (cnt > 0)
                            return true;
                    }
                }
            }
            catch
            { }
            return false;
        }

        //Returns true only if there are any Damages available
        internal static bool CheckDamagesAvailability()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT COUNT(*) FROM Damage", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                    {
                        int cnt = 0;
                        int.TryParse(ds.Rows[0][0].ToString(), out cnt);
                        if (cnt > 0)
                            return true;
                    }
                }
            }
            catch
            { }
            return false;
        }

        //Returns true only if there are any Expenses available
        internal static bool CheckExpensesAvailability()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT COUNT(*) FROM Expense", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                    {
                        int cnt = 0;
                        int.TryParse(ds.Rows[0][0].ToString(), out cnt);
                        if (cnt > 0)
                            return true;
                    }
                }
            }
            catch
            { }
            return false;
        }

        //Returns true only if there are any Amounts available
        internal static bool CheckAmountsAvailability()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT COUNT(*) FROM Amounts", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                    {
                        int cnt = 0;
                        int.TryParse(ds.Rows[0][0].ToString(), out cnt);
                        if (cnt > 0)
                            return true;
                    }
                }
            }
            catch
            { }
            return false;
        }

        //Returns true only if there are any Bulk Orders available
        internal static bool CheckBulkOrdersAvailability()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT COUNT(*) FROM BulkOrder WHERE DATEDIFF(day, dateadd(day,datediff(day,0,getdate())-10,0), BookedDate) > 0", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                    {
                        int cnt = 0;
                        int.TryParse(ds.Rows[0][0].ToString(), out cnt);
                        if (cnt > 0)
                            return true;
                    }
                }
            }
            catch
            { }
            return false;
        }

        internal static int TransferBillData()
        {
            SqlTransaction trans = null;
            SqlDataAdapter adapter = null;

            try
            {
                //Fetching Total Row Count From Local Bill Table
                int rowsCnt = 0;
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT COUNT(*) FROM Bill", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    double val = ((double)rowsCnt / 100);
                    val = Math.Ceiling(val);
                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        //adapter = new SqlDataAdapter("SELECT TOP 100 ID, BillNo, DiscountByPercentage, DiscountByCost, TotalAmount, TotalNetAmount, CashPayment, SodexhoPayment, CardPayment, IsBillCancelled, Branch, CONVERT(DATETIME,BillDate,101), User, CancelledReason, IsSpecialBill, DiscountReason FROM Bill ORDER BY ID", connection);
                        adapter = new SqlDataAdapter("SELECT TOP 100 * FROM Bill ORDER BY ID", connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (connectionMain.State == ConnectionState.Closed)
                                connectionMain.Open();
                            trans = connectionMain.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                foreach (DataRow row in ds.Rows)
                                {
                                    cmd.CommandText = string.Format("exec CentralBillItemAdd '{0}', {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, '{9}', '{10}', '{11}', '{12}', {13}, '{14}'", row[1].ToString(), (row[2].ToString() == string.Empty) ? "0" : row[2].ToString(), (row[3].ToString() == string.Empty) ? "0" : row[3].ToString(), row[4].ToString(), row[5].ToString(), (row[6].ToString() == string.Empty) ? "0" : row[6].ToString(), (row[7].ToString() == string.Empty) ? "0" : row[7].ToString(), (row[8].ToString() == string.Empty) ? "0" : row[8].ToString(), row[9].ToString(), row[10].ToString(), row[11].ToString(), row[12].ToString(), row[13].ToString(), row[14].ToString(), row[15].ToString());
                                    cmd.Connection = connectionMain;
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery().ToString();
                                }
                            }

                            trans.Commit();

                            //Deleting First 10 Rows in Local Database
                            using (SqlCommand cmd = new SqlCommand("DELETE FROM Bill WHERE ID IN (SELECT Top 100 ID FROM Bill ORDER BY ID)", connection))
                            {
                                if (connection.State == ConnectionState.Closed)
                                    connection.Open();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                return 1;
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show(ex.Message);
                return -1;
            }
            finally
            {
                connection.Close();
                connectionMain.Close();
                trans.Dispose();
                adapter.Dispose();
            }
        }

        internal static int TransferBillDetailData()
        {
            SqlTransaction trans = null;
            SqlDataAdapter adapter = null;
            try
            {
                //Fetching Total Row Count From Local BillDetail Table
                int rowsCnt = 0;
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT COUNT(*) FROM BillDetail", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    double val = ((double)rowsCnt / 100);
                    val = Math.Ceiling(val);
                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        adapter = new SqlDataAdapter("SELECT TOP 100 * FROM BillDetail ORDER BY ID", connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (connectionMain.State == ConnectionState.Closed)
                                connectionMain.Open();
                            trans = connectionMain.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                foreach (DataRow row in ds.Rows)
                                {
                                    cmd.CommandText = string.Format("exec CentralBillDetailItemAdd '{0}', {1}, {2}, {3}, '{4}'", row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString());
                                    cmd.Connection = connectionMain;
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery().ToString();
                                }
                            }

                            trans.Commit();

                            //Deleting First 10 Rows in Local Database
                            using (SqlCommand cmd = new SqlCommand("DELETE FROM BillDetail WHERE ID IN (SELECT Top 100 ID FROM BillDetail ORDER BY ID)", connection))
                            {
                                if (connection.State != ConnectionState.Open)
                                    connection.Open();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                connection.Close();
                connectionMain.Close();
                trans.Dispose();
                adapter.Dispose();
            }
        }

        internal static int CopyCakeDetailData()
        {
            SqlTransaction trans = null;
            SqlDataAdapter adapter = null;
            try
            {
                //Fetching total cake bill rows for last 5 days
                int rowsCnt = 0;
                int days = 5;
                string strDays = ConfigurationManager.AppSettings["CakeTransferDays"];

                if (strDays != null && strDays != string.Empty)
                {
                    if (!(int.TryParse(strDays, out days)))
                        days = 5;
                }
                DataTable ds = new DataTable();
                //TODO
                adapter = new SqlDataAdapter(string.Format("SELECT COUNT(*) FROM Cake WHERE DATEDIFF(day, dateadd(day,datediff(day,0,getdate())-{0},0), BookedDate) > 0", days.ToString()), connection);

                //SqlDataAdapter adapter = new SqlDataAdapter("SELECT COUNT(*) FROM Cake WHERE DATEDIFF(day, dateadd(day,datediff(day,0,getdate())-9,0), BookedDate) > 0", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    int idcntr = 0;
                    //TODO
                    double val = ((double)rowsCnt / 100);
                    //double val = ((double)rowsCnt / 10);
                    val = Math.Ceiling(val);
                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        StringBuilder query = new StringBuilder();
                        //TODO
                        query.Append("SELECT TOP 100 * FROM Cake WHERE DATEDIFF(day, dateadd(day,datediff(day,0,getdate())-{0},0), BookedDate) > 0");
                        //query.Append("SELECT TOP 10 * FROM Cake WHERE DATEDIFF(day, dateadd(day,datediff(day,0,getdate())-9,0), BookedDate) > 0");
                        if (idcntr != 0)
                            query.Append(" AND ID > " + idcntr);

                        adapter = new SqlDataAdapter(string.Format(query.ToString(), days.ToString()), connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (connectionMain.State != ConnectionState.Open)
                                connectionMain.Open();
                            trans = connectionMain.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                foreach (DataRow row in ds.Rows)
                                {
                                    //cmd.CommandText = string.Format("exec CopyTransferCakeDetails '{0}', '{1}', '{2}', '{3}', '{4}', {5}, '{6}', '{7}', '{8}', {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, '{23}', {24}, '{25}', {26}, '{27}', {28}, '{29}', '{30}'",
                                    cmd.CommandText = string.Format("exec CopyTransferCakeDetailsUpdated '{0}', '{1}', '{2}', '{3}', '{4}', {5}, '{6}', '{7}', '{8}', {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, '{23}', {24}, '{25}', {26}, '{27}', {28}, '{29}', '{30}', '{31}', '{32}'",
                                        row[1].ToString(),
                                        row[2].ToString(),
                                        row[3].ToString(),
                                        Convert.ToDateTime(row[4].ToString()).ToString("MM/dd/yyyy hh:mm tt"),
                                        row[5].ToString(),
                                        row[6].ToString(),
                                        row[7].ToString(),
                                        row[8].ToString(),
                                        row[9].ToString(),
                                        row[10].ToString(),
                                        row[11].ToString(),
                                        row[12].ToString(),
                                        (row[13].ToString() == string.Empty) ? "0" : row[13].ToString(),
                                        (row[14].ToString() == string.Empty) ? "0" : row[14].ToString(),
                                        (row[15].ToString() == string.Empty) ? "0" : row[15].ToString(),
                                        (row[16].ToString() == string.Empty) ? "0" : row[16].ToString(),
                                        (row[17].ToString() == string.Empty) ? "0" : row[17].ToString(),
                                        (row[18].ToString() == string.Empty) ? "0" : row[18].ToString(),
                                        (row[19].ToString() == string.Empty) ? "0" : row[19].ToString(),
                                        (row[20].ToString() == string.Empty) ? "0" : row[20].ToString(),
                                        (row[21].ToString() == string.Empty) ? "0" : row[21].ToString(),
                                        row[22].ToString(),
                                        row[23].ToString(),
                                        Convert.ToDateTime(row[24].ToString()).ToString("MM/dd/yyyy hh:mm tt"),
                                        row[25].ToString(),
                                        (row[26].ToString() == string.Empty) ? "null" : row[26].ToString(),
                                        row[27].ToString(),
                                        row[28].ToString(),
                                        row[29].ToString(),
                                        row[30].ToString(),
                                        row[31].ToString(),
                                        row[32].ToString(),
                                        row[33].ToString());
                                    cmd.CommandText = cmd.CommandText.Replace("'null'", "null");
                                    cmd.Connection = connectionMain;
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery().ToString();
                                    idcntr = int.Parse(row[0].ToString());
                                }
                                trans.Commit();
                            }
                        }
                    }
                }
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                connection.Close();
                connectionMain.Close();
                trans.Dispose();
                adapter.Dispose();
            }
        }

        internal static int TransferCakeBalance()
        {
            SqlDataAdapter adapter = null;
            SqlTransaction trans = null;
            try
            {
                //Fetching Total Row Count From Local Bill Table
                int rowsCnt = 0;
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT COUNT(*) FROM CakeBalance", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    double val = ((double)rowsCnt / 100);
                    val = Math.Ceiling(val);
                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        adapter = new SqlDataAdapter("SELECT TOP 100 * FROM CakeBalance ORDER BY ID", connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (connectionMain.State != ConnectionState.Open)
                                connectionMain.Open();
                            trans = connectionMain.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                foreach (DataRow row in ds.Rows)
                                {
                                    cmd.CommandText = string.Format("exec CentralCakeBalanceAdd '{0}', '{1}', '{2}', '{3}'", row[1].ToString(), row[2].ToString(), row[3].ToString(), DateTime.Parse(row[4].ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
                                    cmd.Connection = connectionMain;
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery().ToString();
                                }
                            }

                            trans.Commit();

                            //Deleting First 10 Rows in Local Database
                            using (SqlCommand cmd = new SqlCommand("DELETE FROM CakeBalance WHERE ID IN (SELECT Top 100 ID FROM CakeBalance ORDER BY ID)", connection))
                            {
                                if (connection.State == ConnectionState.Closed)
                                    connection.Open();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                connection.Close();
                connectionMain.Close();
                trans.Dispose();
                adapter.Dispose();
            }
        }

        internal static int TransferCreditNotesData()
        {
            SqlDataAdapter adapter = null;
            SqlTransaction trans = null;
            try
            {
                //Fetching Total Row Count From Local Bill Table
                int rowsCnt = 0;
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT COUNT(*) FROM CreditNote", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    double val = ((double)rowsCnt / 100);
                    val = Math.Ceiling(val);
                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        adapter = new SqlDataAdapter("SELECT TOP 100 * FROM CreditNote ORDER BY ID", connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (connectionMain.State == ConnectionState.Closed)
                                connectionMain.Open();
                            trans = connectionMain.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                foreach (DataRow row in ds.Rows)
                                {
                                    cmd.CommandText = string.Format("exec CentralCreditNotesAdd '{0}', {1}, {2}, '{3}', '{4}'", row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString());
                                    cmd.Connection = connectionMain;
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery().ToString();
                                }
                            }

                            trans.Commit();

                            //Deleting First 10 Rows in Local Database
                            using (SqlCommand cmd = new SqlCommand("DELETE FROM CreditNote WHERE ID IN (SELECT Top 100 ID FROM CreditNote ORDER BY ID)", connection))
                            {
                                if (connection.State == ConnectionState.Closed)
                                    connection.Open();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                connection.Close();
                connectionMain.Close();
                adapter.Dispose();
                trans.Dispose();
            }
        }

        internal static int TransferDamagesData()
        {
            SqlDataAdapter adapter = null;
            SqlTransaction trans = null;
            try
            {
                //Fetching Total Row Count From Local Bill Table
                int rowsCnt = 0;
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT COUNT(*) FROM Damage", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    double val = ((double)rowsCnt / 100);
                    val = Math.Ceiling(val);
                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        adapter = new SqlDataAdapter("SELECT TOP 100 * FROM Damage ORDER BY ID", connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (connectionMain.State != ConnectionState.Open)
                                connectionMain.Open();
                            trans = connectionMain.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                foreach (DataRow row in ds.Rows)
                                {
                                    cmd.CommandText = string.Format("exec CentralDamagesAdd '{0}', '{1}', {2}, {3}, '{4}'", row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString());
                                    cmd.Connection = connectionMain;
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery().ToString();
                                }
                            }

                            trans.Commit();

                            //Deleting First 10 Rows in Local Database
                            using (SqlCommand cmd = new SqlCommand("DELETE FROM Damage WHERE ID IN (SELECT Top 100 ID FROM Damage ORDER BY ID)", connection))
                            {
                                if (connection.State == ConnectionState.Closed)
                                    connection.Open();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                connection.Close();
                connectionMain.Close();
                trans.Dispose();
                adapter.Dispose();
            }
        }

        internal static int TransferExpensesData()
        {
            SqlDataAdapter adapter = null;
            SqlTransaction trans = null;
            try
            {
                //Fetching Total Row Count From Local Bill Table
                int rowsCnt = 0;
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT COUNT(*) FROM Expense", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    double val = ((double)rowsCnt / 100);
                    val = Math.Ceiling(val);
                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        adapter = new SqlDataAdapter("SELECT TOP 100 * FROM Expense ORDER BY ID", connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (connectionMain.State != ConnectionState.Open)
                                connectionMain.Open();
                            trans = connectionMain.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                foreach (DataRow row in ds.Rows)
                                {
                                    cmd.CommandText = string.Format("exec CentralExpensesAdd '{0}', {1}, '{2}', {3}, '{4}', '{5}'", row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString());
                                    cmd.Connection = connectionMain;
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery().ToString();
                                }
                            }

                            trans.Commit();

                            //Deleting First 10 Rows in Local Database
                            using (SqlCommand cmd = new SqlCommand("DELETE FROM Expense WHERE ID IN (SELECT Top 100 ID FROM Expense ORDER BY ID)", connection))
                            {
                                if (connection.State == ConnectionState.Closed)
                                    connection.Open();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                connection.Close();
                connectionMain.Close();
                trans.Dispose();
                adapter.Dispose();
            }
        }

        internal static int TransferAmountsData()
        {
            SqlDataAdapter adapter = null;
            //SqlTransaction trans = null;
            try
            {
                //Fetching Total Row Count From Local Bill Table
                int rowsCnt = 0;
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT COUNT(*) FROM Amounts", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    double val = ((double)rowsCnt / 100);
                    val = Math.Ceiling(val);
                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        adapter = new SqlDataAdapter("SELECT TOP 100 * FROM Amounts ORDER BY ID", connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (connectionMain.State == ConnectionState.Closed)
                                connectionMain.Open();
                            //trans = connectionMain.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                foreach (DataRow row in ds.Rows)
                                {
                                    cmd.CommandText = string.Format("exec CentralAmountsAdd '{0}', {1}, {2}, '{3}', {4}, '{5}'", row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString());
                                    cmd.Connection = connectionMain;
                                    //cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery();
                                }
                            }

                            //trans.Commit();

                            //Deleting First 100 Rows in Local Database
                            using (SqlCommand cmd = new SqlCommand("DELETE FROM Amounts WHERE ID IN (SELECT Top 100 ID FROM Amounts ORDER BY ID)", connection))
                            {
                                if (connection.State != ConnectionState.Open)
                                    connection.Open();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                connection.Close();
                connectionMain.Close();
                //trans.Dispose();
                adapter.Dispose();
            }
        }

        internal static bool RemainingStockSnapShotToLocal()
        {
            SqlDataAdapter adapter = null;
            string str = string.Empty;
            try
            {
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT ItemCode, StockAvailable FROM MasterItems MI JOIN Stock S ON MI.StockID = S.StockID", connection);
                adapter.Fill(ds);

                if (ds != null && ds.Rows.Count > 0)
                {

                    if (connection.State != ConnectionState.Open)
                        connection.Open();

                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = connection;
                        cmd.CommandType = CommandType.Text;
                        foreach (DataRow row in ds.Rows)
                        {
                            cmd.CommandText = string.Format("exec RemainingStockAdd '{0}', '{1}', '{2}'", row[0].ToString(), row[1].ToString(), DateTime.Now.ToString("MM-dd-yyyy"));
                            str = cmd.CommandText;
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                //System.Windows.MessageBox.Show("RemainingStockSnapShotToLocal - " + str + " - " + ex.Message);
                return false;
            }
            finally
            {
                connection.Close();
            }
        }

        internal static int TransferSnapShotData()
        {
            SqlDataAdapter adapter = null;
            SqlTransaction trans = null;
            try
            {
                //Fetching Total Row Count From Local Bill Table
                int rowsCnt = 0;
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT COUNT(*) FROM RemainingStock", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    double val = ((double)rowsCnt / 100);
                    val = Math.Ceiling(val);
                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        adapter = new SqlDataAdapter("SELECT TOP 100 * FROM RemainingStock ORDER BY ID", connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (connectionMain.State == ConnectionState.Closed)
                                connectionMain.Open();
                            trans = connectionMain.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                int value = 0;
                                foreach (DataRow row in ds.Rows)
                                {
                                    cmd.CommandText = string.Format("exec CentralRemainingStockAdd '{0}', '{1}', '{2}', '{3}'", row[1].ToString(), row[2].ToString(), Branch, Convert.ToDateTime(row[3].ToString()).ToString("MM/dd/yyyy hh:mm tt"));
                                    cmd.Connection = connectionMain;
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    value += cmd.ExecuteNonQuery();
                                }
                                //System.Windows.MessageBox.Show(value.ToString());
                            }

                            trans.Commit();

                            //Deleting First 10 Rows in Local Database
                            using (SqlCommand cmd = new SqlCommand("DELETE FROM RemainingStock WHERE ID IN (SELECT Top 100 ID FROM RemainingStock ORDER BY ID)", connection))
                            {
                                if (connection.State != ConnectionState.Open)
                                    connection.Open();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                return 1;
            }
            catch (Exception ex)
            {
                //System.Windows.MessageBox.Show(ex.Message);
                return -1;
            }
            finally
            {
                connection.Close();
                connectionMain.Close();
                trans.Dispose();
                adapter.Dispose();
            }
        }

        internal static int CopyRemainingStock()
        {
            SqlTransaction trans = null;
            SqlDataAdapter adapter = null;
            try
            {
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT ItemCode, StockAvailable FROM MasterItems MI JOIN Stock S ON MI.StockID = S.StockID", connection);
                adapter.Fill(ds);

                if (ds != null && ds.Rows.Count > 0)
                {
                    if (connectionMain.State != ConnectionState.Open)
                        connectionMain.Open();
                    trans = connectionMain.BeginTransaction();

                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = connectionMain;
                        cmd.Transaction = trans;
                        cmd.CommandType = CommandType.Text;

                        foreach (DataRow row in ds.Rows)
                        {
                            cmd.CommandText = string.Format("exec CentralCurrentRemainingStockAdd {0}, {1}, {2}, '{3}'", row[0].ToString(), row[1].ToString(), Branch, DateTime.Now.ToString("MM-dd-yyyy"));
                            cmd.ExecuteNonQuery();
                        }
                    }

                    trans.Commit();
                }

                return 1;
            }
            catch (Exception ex)
            {
                //System.Windows.MessageBox.Show(ex.Message);
                if (trans != null)
                    trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                connectionMain.Close();
                trans.Dispose();
                adapter.Dispose();
            }
        }

        internal static int CopyBulkOrdersData()
        {
            SqlDataAdapter adapter = null;
            SqlTransaction trans = null;

            try
            {
                //Fetching total bulk order rows for last 10 days
                int rowsCnt = 0;
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT COUNT(*) FROM BulkOrder WHERE DATEDIFF(day, dateadd(day,datediff(day,0,getdate())-10,0), BookedDate) > 0", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    int idcntr = 0;
                    double val = ((double)rowsCnt / 100);
                    val = Math.Ceiling(val);
                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        StringBuilder query = new StringBuilder();
                        query.Append("SELECT TOP 100 * FROM BulkOrder WHERE DATEDIFF(day, dateadd(day,datediff(day,0,getdate())-10,0), BookedDate) > 0");

                        if (idcntr != 0)
                            query.Append(" AND ID > " + idcntr);

                        adapter = new SqlDataAdapter(query.ToString(), connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (connectionMain.State != ConnectionState.Open) 
                                connectionMain.Open();
                            trans = connectionMain.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                foreach (DataRow row in ds.Rows)
                                {
                                    cmd.CommandText = string.Format("exec CopyBulkOrderDetailsVersion2 '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '{20}', '{21}', '{22}', '{23}'",
                                        row[1].ToString(),
                                        (row[2].ToString() == string.Empty) ? "0" : row[2].ToString(),
                                        row[3].ToString(),
                                        Convert.ToDateTime(row[4].ToString()).ToString("MM/dd/yyyy hh:mm tt"),
                                        row[5].ToString(),
                                        row[6].ToString(),
                                        row[7].ToString(),
                                        row[8].ToString(),
                                        Convert.ToDateTime(row[9].ToString()).ToString("MM/dd/yyyy hh:mm tt"),
                                        row[10].ToString(),
                                        row[11].ToString(),
                                        row[12].ToString(),
                                        row[13].ToString(),
                                        row[14].ToString(),
                                        row[15].ToString(),
                                        row[16].ToString(),
                                        row[17].ToString(),
                                        row[18].ToString(),
                                        row[19].ToString(),
                                        row[20].ToString(),
                                        row[21].ToString(),
                                        row[22].ToString() == string.Empty ? "null" : Convert.ToDateTime(row[22].ToString()).ToString("MM/dd/yyyy hh:mm tt"),
                                        row[23].ToString(),
                                        row[24].ToString());
                                    cmd.CommandText = cmd.CommandText.Replace("'null'", "null");
                                    cmd.Connection = connectionMain;
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery().ToString();
                                    idcntr = int.Parse(row[0].ToString());
                                }
                            }

                            trans.Commit();
                        }
                    }
                }
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                connection.Close();
                connectionMain.Close();
                trans.Dispose();
                adapter.Dispose();
            }
        }

        internal static DataTable GetOtherBranchItems(string branch, string item)
        {
            SqlConnection con = null;
            SqlDataAdapter adapter = null;
            try
            {
                if (branch == "1")
                    con = new SqlConnection(conStringChandanagar);
                else if (branch == "2")
                    con = new SqlConnection(conStringMiyapur);
                else if (branch == "3")
                    con = new SqlConnection(conStringKukatpally);
                else if (branch == "4")
                    con = new SqlConnection(conStringAmeerpet);
                else if (branch == "5")
                    con = new SqlConnection(conStringJNTU);

                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter(string.Format("SELECT ItemCode, ItemName, SellingPrice, S.StockAvailable FROM MasterItems MI JOIN  Stock S ON MI.StockID = S.StockID WHERE ItemCode like '{0}%'", item), con);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    return ds;
            }
            catch
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    adapter.Dispose();
                }
            }
            return null;
        }

        internal static void SetCakeChefCopyPrinted(string cakeNo)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("UPDATE Cake Set IsChefCopyPrinted = 1 WHERE CakeNo = '{0}'", cakeNo), connection))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            { }
            finally
            {
                connection.Close();
            }
        }

        internal static DataTable GetAllBulkOrdersByDeliveryDate(DateTime dateTime)
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT BillNo, CustomerName, PhoneNo, CONVERT(VARCHAR, BookedDate, 106) AS BookedDate, CONVERT(VARCHAR, DeliveryDate, 106) AS DeliveryDate, Advance, Balance, DiscountByCost AS 'DiscRs', DiscountByPercentage AS 'DiscPerc', TotalAmount AS Amount, DeliveryTime, DeliveryPlace, IsOrderDelivered FROM BulkOrder WHERE IsOrderCancelled = 0 AND DATEDIFF(day, '{0}', DeliveryDate) = 0 ORDER BY ID DESC", dateTime.ToString("MM-dd-yyyy")), connection))
                {
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                        return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static DataTable GetAllBulkOrdersByPhoneNo(string phoneNo)
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT BillNo, CustomerName, PhoneNo, CONVERT(VARCHAR, BookedDate, 106) AS BookedDate, CONVERT(VARCHAR, DeliveryDate, 106) AS DeliveryDate, Advance, Balance, DiscountByCost AS 'DiscRs', DiscountByPercentage AS 'DiscPerc', TotalAmount AS Amount, DeliveryTime, DeliveryPlace, IsOrderDelivered FROM BulkOrder WHERE IsOrderCancelled = 0 AND PhoneNo like '%{0}%' ORDER BY ID DESC", phoneNo), connection))
                {
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                        return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static DataTable GetVendorCompanies()
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ID, Vendor FROM MasterVendors", connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static DataTable GetItemDetailsForVendor(int vendorID, string itemCode)
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("exec GetVendorItemDetails {0}, {1}", vendorID, itemCode), connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static string GenerateBillForVendor(ItemCollection items, double totalAmt, VendorDto vendor)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            string res = string.Empty;
            SqlTransaction trans = connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddBillItemForVendor {0}, {1}, '{2}'", totalAmt, Storage.LoginUser, BranchLetter)))
                {
                    cmd.Connection = connection;
                    cmd.Transaction = trans;

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable ds = new DataTable();
                        adapter.Fill(ds);

                        if (!(ds.Rows.Count >= 0))
                            throw new Exception();
                        res = ds.Rows[0][0].ToString();
                    }

                    foreach (VendorBillingGridItemControl item in items)
                    {
                        if (item.txtCode.Text != string.Empty)
                        {
                            cmd.CommandText = string.Format("exec AddBillDetailItem '{0}', {1}, {2}", res, item.txtCode.Text, item.txtQty.Text);
                            int result = cmd.ExecuteNonQuery();
                            if (result <= 0)
                                throw new Exception();
                        }
                    }

                    cmd.CommandText = string.Format("exec AddVendorSale {0}, {1}, {2}, {3}", vendor.VendorID, vendor.NetTotalAmount, res, Branch);
                    cmd.ExecuteNonQuery();
                }

                trans.Commit();
                return res;
            }
            catch (Exception)
            {
                trans.Rollback();
                return string.Empty;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static DataTable GetCreditNoteBill(string billNo)
        {
            try
            {
                DataTable ds = new DataTable();
                StringBuilder query = new StringBuilder();
                //SELECT MI.ItemCode, MI.ItemName, MI.SellingPrice, BD.Qty FROM BillDetail BD JOIN MasterItems MI ON BD.Item = MI.ItemCode WHERE BillNo = '{0}';", billNo), connection);
                //query.Append("SELECT MI.ItemCode, MI.ItemName, BD.Qty, SellingPrice = " + 
                //                " CASE " +
                //                        " WHEN ((SELECT SellingPrice FROM CreditNoteRates CNR WHERE CNR.CreditNoteCompanyID = {1} AND BD.Item = CNR.ItemCode) IS NOT NULL) " +
                //                                " THEN (SELECT SellingPrice FROM CreditNoteRates CNR WHERE CNR.CreditNoteCompanyID = {1} AND BD.Item = CNR.ItemCode) " +
                //                        " ELSE (SELECT SellingPrice FROM MasterItems WHERE ItemCode = BD.Item) " + 
                //                " END " +
                //            " FROM BillDetail BD " + 
                //            " JOIN MasterItems MI ON BD.Item = MI.ItemCode " + 
                //            " WHERE BillNo = '{0}' ");

                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT B.BillNo, MCN.CreditNoteCompany, B.[User] AS Cashier, B.BillDate, TotalAmount, MCN.ID AS CompanyID FROM CreditNote CN JOIN MasterCreditNotes MCN ON CN.CreditNoteCompany = MCN.ID JOIN Bill B ON B.BillNo = CN.BillNo WHERE CN.BillNo = '{0}' AND B.BillNo = '{0}' AND IsBillCancelled = 0;", billNo), connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static DataTable GetCreditNoteDetailedBill(string billNo, string companyID)
        {
            try
            {
                DataTable ds = new DataTable();
                StringBuilder query = new StringBuilder();
                //query.Append("SELECT B.BillNo, MCN.CreditNoteCompany, B.[User] AS Cashier, B.BillDate, TotalAmount, MCN.ID AS CompanyID FROM CreditNote CN JOIN MasterCreditNotes MCN ON CN.CreditNoteCompany = MCN.ID JOIN Bill B ON B.BillNo = CN.BillNo WHERE CN.BillNo = '{0}' AND B.BillNo = '{0}' AND IsBillCancelled = 0;");
                query.Append("SELECT MI.ItemCode, MI.ItemName, BD.Qty, SellingPrice = " +
                                " CASE " +
                                        " WHEN ((SELECT SellingPrice FROM CreditNoteRates CNR WHERE CNR.CreditNoteCompanyID = {1} AND BD.Item = CNR.ItemCode) IS NOT NULL) " +
                                                " THEN (SELECT SellingPrice FROM CreditNoteRates CNR WHERE CNR.CreditNoteCompanyID = {1} AND BD.Item = CNR.ItemCode) " +
                                        " ELSE (SELECT SellingPrice FROM MasterItems WHERE ItemCode = BD.Item) " +
                                " END " +
                            " FROM BillDetail BD " +
                            " JOIN MasterItems MI ON BD.Item = MI.ItemCode " +
                            " WHERE BillNo = '{0}' ");

                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format(query.ToString(), billNo, companyID), connection))
                {
                    //SELECT MI.ItemCode, MI.ItemName, MI.SellingPrice, BD.Qty FROM BillDetail BD JOIN MasterItems MI ON BD.Item = MI.ItemCode WHERE BillNo = '{0}';", billNo), connection);
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static List<Supplier> GetSuppliers()
        {
            try
            {
                if (connectionFinancial.State != ConnectionState.Open)
                    connectionFinancial.Open();
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT SupplierID, SupplierName FROM MasterSuppliers ORDER BY SupplierID", connectionFinancial))
                {
                    adapter.Fill(ds);
                    if (ds != null)
                    {
                        List<Supplier> suppliers = new List<Supplier>();
                        foreach (DataRow row in ds.Rows)
                            suppliers.Add(new Supplier { SupplierID = int.Parse(row[0].ToString()), SupplierName = row[1].ToString() });
                        return suppliers;
                    }
                }
            }
            catch { }
            return null;
        }

        internal static int SavePayment(string supplierID, string billNo, string amount, string billDate)
        {
            if (connectionFinancial.State != ConnectionState.Open)
                connectionFinancial.Open();
            SqlTransaction trans = connectionFinancial.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("INSERT INTO [SupplierBillDetails] ([SupplierID], [BillNo], [PaymentAmount], [BillDate], [Branch]) VALUES ({0}, '{1}', '{2}', '{3}', '{4}')", supplierID, billNo, amount, billDate, ConfigurationManager.AppSettings["Branch"].ToString()), connectionFinancial, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return Int32.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connection.Close();
                trans.Dispose();
            }
        }

        internal static DataTable GetPayments()
        {
            try
            {
                if (connectionFinancial.State != ConnectionState.Open)
                    connectionFinancial.Open();

                DataTable ds = new DataTable();
                StringBuilder query = new StringBuilder();
                query.Append("SELECT SupplierName, BillNo, PaymentAmount AS Amount FROM SupplierBillDetails SBD JOIN MasterSuppliers MS ON SBD.SupplierID = MS.SupplierID WHERE Branch = {0} AND DATEDIFF(day, '{1}', Date) = 0 ORDER BY BillID DESC");
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format(query.ToString(), ConfigurationManager.AppSettings["Branch"].ToString(), DateTime.Now.ToString("MM/dd/yyyy")), connectionFinancial))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static bool CheckVendorBillsAvailability()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT COUNT(*) FROM VendorSales", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                    {
                        int cnt = 0;
                        if (int.TryParse(ds.Rows[0][0].ToString(), out cnt))
                        {
                            if (cnt > 0)
                                return true;
                        }
                    }
                }
            }
            catch
            { }
            return false;
        }

        internal static int TransferVendorBillsData()
        {
            SqlDataAdapter adapter = null;
            SqlTransaction trans = null;
            try
            {
                //Fetching Total Row Count From Local VendorSales Table
                int rowsCnt = 0;
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT COUNT(*) FROM VendorSales", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    double val = ((double)rowsCnt / 100);
                    val = Math.Ceiling(val);
                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        adapter = new SqlDataAdapter("SELECT TOP 100 * FROM VendorSales ORDER BY ID", connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (connectionMain.State != ConnectionState.Open)
                                connectionMain.Open();
                            trans = connectionMain.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                foreach (DataRow row in ds.Rows)
                                {
                                    cmd.CommandText = string.Format("exec CentralVendorBillAdd '{0}', {1}, {2}, '{3}', '{4}'", row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString());
                                    cmd.Connection = connectionMain;
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery().ToString();
                                }
                            }

                            trans.Commit();

                            //Deleting First 10 Rows in Local Database
                            using (SqlCommand cmd = new SqlCommand("DELETE FROM VendorSales WHERE ID IN (SELECT Top 100 ID FROM VendorSales ORDER BY ID)", connection))
                            {
                                if (connection.State == ConnectionState.Closed)
                                    connection.Open();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                connection.Close();
                connectionMain.Close();
                trans.Dispose();
                adapter.Dispose();
            }
        }

        internal static bool CheckSalaryDetailsAvailability()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("select(select count(*) from CreditTransactions)+(select count(*) from DebitTransactions)", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                    {
                        int cnt = 0;
                        if (int.TryParse(ds.Rows[0][0].ToString(), out cnt))
                        {
                            if (cnt > 0)
                                return true;
                        }
                    }
                }
            }
            catch
            { }
            return false;
        }

        internal static int TransferSalariesData()
        {
            //SyncEmployeesFromServer();
            if (TransferSalariesCreditTransactions() + TransferSalariesDebitTransactions() > 0)
                return 1;
            else
                return -1;
        }

        //private static void SyncEmployeesFromServer()
        //{
        //    try
        //    {

        //    }
        //    catch (Exception) { }
        //}

        internal static int TransferSalariesCreditTransactions()
        {
            SqlDataAdapter adapter = null;
            SqlTransaction trans = null;
            try
            {
                //Fetching Total Row Count From Local Credit Transactions Table
                int rowsCnt = 0;
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT COUNT(*) FROM CreditTransactions", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    double val = ((double)rowsCnt / 100);
                    val = Math.Ceiling(val);
                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        adapter = new SqlDataAdapter("SELECT TOP 100 * FROM CreditTransactions ORDER BY CreditTransactionID", connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (salariesConnection.State != ConnectionState.Open)
                                salariesConnection.Open();
                            trans = salariesConnection.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                foreach (DataRow row in ds.Rows)
                                {
                                    cmd.CommandText = string.Format("exec AddLeaveOrOTFromBranch '{0}', '{1}', '{2}', '{3}', '{4}'", row[1].ToString(), row[2].ToString(), row[4].ToString(), row[3].ToString(), row[6].ToString());
                                    cmd.Connection = salariesConnection;
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery().ToString();
                                }
                            }

                            trans.Commit();

                            //Deleting First 10 Rows in Local Database
                            using (SqlCommand cmd = new SqlCommand("DELETE FROM CreditTransactions WHERE CreditTransactionID IN (SELECT Top 100 CreditTransactionID FROM CreditTransactions ORDER BY CreditTransactionID)", connection))
                            {
                                if (connection.State == ConnectionState.Closed)
                                    connection.Open();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                if (connection != null)
                    connection.Close();
                if (salariesConnection != null)
                    salariesConnection.Close();
                if (trans != null)
                    trans.Dispose();
                if (adapter != null)
                    adapter.Dispose();
            }
        }

        internal static int TransferSalariesDebitTransactions()
        {
            SqlDataAdapter adapter = null;
            SqlTransaction trans = null;
            try
            {
                //Fetching Total Row Count From Local Debit Transactions Table
                int rowsCnt = 0;
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT COUNT(*) FROM DebitTransactions", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                {
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());
                }

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    double val = ((double)rowsCnt / 100);
                    val = Math.Ceiling(val);
                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        adapter = new SqlDataAdapter("SELECT TOP 100 * FROM DebitTransactions ORDER BY DebitTransactionID", connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (salariesConnection.State != ConnectionState.Open)
                                salariesConnection.Open();
                            trans = salariesConnection.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                foreach (DataRow row in ds.Rows)
                                {
                                    cmd.CommandText = string.Format("exec PayAdvanceOrBillFromBranch '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}'", row[1].ToString(), row[3].ToString(), row[2].ToString(), DateTime.Parse(row[4].ToString()).ToString("yyyy-MM-dd"), row[5].ToString(), DateTime.Parse(row[6].ToString()).ToString("yyyy-MM-dd HH:mm:ss"), row[7].ToString(), row[8].ToString());
                                    cmd.Connection = salariesConnection;
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery().ToString();
                                }
                            }

                            trans.Commit();

                            //Deleting First 100 Rows in Local Database
                            using (SqlCommand cmd = new SqlCommand("DELETE FROM DebitTransactions WHERE DebitTransactionID IN (SELECT Top 100 DebitTransactionID FROM DebitTransactions ORDER BY DebitTransactionID)", connection))
                            {
                                if (connection.State == ConnectionState.Closed)
                                    connection.Open();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                return 1;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                if (connection != null)
                    connection.Close();
                if (salariesConnection != null)
                    salariesConnection.Close();
                if (trans != null)
                    trans.Dispose();
                if (adapter != null)
                    adapter.Dispose();
            }
        }

        internal static int AddOrder(DateTime date, string branch, string user, string xml)
        {
            if (connectionManufacturing.State != ConnectionState.Open)
                connectionManufacturing.Open();
            SqlTransaction trans = connectionManufacturing.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddOrder '{0}', '{1}' , '{2}', '{3}'", date.ToString("yyyy-MM-dd"), branch, user, xml), connectionManufacturing, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return Int32.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                connectionManufacturing.Close();
                trans.Dispose();
            }
        }

        internal static bool CheckInternetConnection()
        {
            try
            {
                if (connectionMain.State != ConnectionState.Open)
                    connectionMain.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT Top 1 * FROM InternetCheck", connectionMain))
                {
                    cmd.CommandTimeout = 10;
                    cmd.ExecuteScalar();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        internal static int SyncRatesFromServer()
        {
            SqlTransaction tran = null;
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ItemCode, ItemName, CostPrice, SellingPrice, Dept, AcceptNegativeStock, AcceptDecimalStock, PopupLimit, IsLessStockShown, 0 AS StockAvailable, GroupID FROM MasterItems", connectionMain))
                {
                    adapter.Fill(ds);
                }

                if (connection.State != ConnectionState.Open)
                    connection.Open();

                tran = connection.BeginTransaction();

                SqlCommand cmdChild = new SqlCommand();
                cmdChild.CommandType = CommandType.Text;
                cmdChild.Connection = connection;
                cmdChild.Transaction = tran;

                foreach (DataRow row in ds.Rows)
                {
                    cmdChild.CommandText = string.Format("exec AddOrUpdateMasterItemWithoutGroup '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}'", row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString(), row[7].ToString(), row[8].ToString(), row[9].ToString(), row[10].ToString());
                    if (cmdChild.ExecuteNonQuery() < 1)
                        throw new Exception();
                }
                tran.Commit();
                return 0;
            }
            catch (Exception)
            {
                tran.Rollback();
                return -1;
            }
            finally
            {
                tran.Dispose();
                connection.Close();
            }
        }

        internal static bool CheckLoginDetailsAvailability()
        {
            try
            {
                DataTable ds = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter("select count(*) from LoginDetails", connection))
                {
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                    {
                        int cnt = 0;
                        if (int.TryParse(ds.Rows[0][0].ToString(), out cnt))
                        {
                            if (cnt > 0)
                                return true;
                        }
                    }
                }
            }
            catch
            { }
            return false;
        }

        internal static int TransferLoginDetails()
        {
            SqlDataAdapter adapter = null;
            SqlTransaction trans = null;
            try
            {
                //Fetching Total Row Count From Local LoginDetails Table
                int rowsCnt = 0;
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT COUNT(*) FROM LoginDetails", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    double val = ((double)rowsCnt / 100);
                    val = Math.Ceiling(val);
                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        adapter = new SqlDataAdapter("SELECT TOP 100 * FROM LoginDetails ORDER BY ID", connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (connectionMain.State != ConnectionState.Open)
                                connectionMain.Open();
                            trans = connectionMain.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                foreach (DataRow row in ds.Rows)
                                {
                                    cmd.CommandText = string.Format("exec CentralLoginDetail '{0}', '{1}', '{2}'", row[1].ToString(), row[2].ToString(), row[3].ToString());
                                    cmd.Connection = connectionMain;
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery().ToString();
                                }
                            }

                            trans.Commit();

                            //Deleting First 10 Rows in Local Database
                            using (SqlCommand cmd = new SqlCommand("DELETE FROM LoginDetails WHERE ID IN (SELECT Top 100 ID FROM LoginDetails ORDER BY ID)", connection))
                            {
                                if (connection.State == ConnectionState.Closed)
                                    connection.Open();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                connection.Close();
                connectionMain.Close();
                trans.Dispose();
                adapter.Dispose();
            }
        }

        internal static int GetToken()
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand("exec GetToken", connection))
                {
                    return int.Parse(cmd.ExecuteScalar().ToString());
                }
            }
            catch (Exception)
            {
                return 0;
            }
            finally
            {
                connection.Close();
            }
        }

        internal static void TransferStockTransactionDetails()
        {
            SqlDataAdapter adapter = null;
            SqlTransaction trans = null;

            try
            {
                int rowsCnt = 0;
                DataTable ds = new DataTable();
                adapter = new SqlDataAdapter("SELECT COUNT(*) FROM StockTransaction", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                {
                    rowsCnt = int.Parse(ds.Rows[0][0].ToString());
                }

                //If Row Count > 0
                if (rowsCnt > 0)
                {
                    double val = ((double)rowsCnt / 100);
                    val = Math.Ceiling(val);

                    for (int cnt = 0; cnt < val; cnt++)
                    {
                        //Fetching first 100 rows
                        adapter = new SqlDataAdapter("SELECT Top 100 * FROM StockTransaction ORDER BY ID", connection);
                        ds = new DataTable();
                        adapter.Fill(ds);

                        if (ds != null && ds.Rows.Count > 0)
                        {
                            if (connectionMain.State != ConnectionState.Open)
                                connectionMain.Open();
                            trans = connectionMain.BeginTransaction();

                            //Inserting/updating First 100 Rows
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                foreach (DataRow row in ds.Rows)
                                {
                                    cmd.CommandText = string.Format("exec CentralAddStockTransaction '{0}', '{1}', '{2}', '{3}', '{4}', '{5}'", row[1].ToString(), row[2].ToString(), DateTime.Parse(row[3].ToString()).ToString("MM-dd-yyyy"), row[4].ToString(), row[5].ToString(), ConfigurationManager.AppSettings["Branch"].ToString());
                                    cmd.Connection = connectionMain;
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery().ToString();
                                }
                                trans.Commit();
                            }

                            //Deleting First 100 Rows in Local Database
                            using (SqlCommand cmd = new SqlCommand("DELETE FROM StockTransaction WHERE ID IN (SELECT Top 100 ID FROM StockTransaction ORDER BY ID)", connection))
                            {
                                if (connection.State == ConnectionState.Closed)
                                    connection.Open();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
            catch (Exception)
            {
                trans.Rollback();
            }
            finally
            {

            }
        }

        internal static int AddCakeBalance(string amount)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();

            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("INSERT INTO CakeBalance (Amount, Cashier, Branch) VALUES ('{0}', '{1}', '{2}')", amount, Storage.LoginUser, Branch), connection))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        return int.Parse(result);
                    }
                }
                return 0;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                connection.Close();
            }
        }

        internal static DataTable GetDetailsByCakeBillNo(string billNo, DateTime bookedDate)
        {
            try
            {
                DataTable ds = new DataTable();

                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT CakeNo, Balance FROM Cake WHERE BillNo = '{0}' AND CashierDelivered = 0 AND DATEDIFF(day, '{1}', BookedDate) = 0 ", billNo, bookedDate.ToString("MM-dd-yyyy")), connection))
                {
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            { }
            return null;
        }

        internal static bool DeliverCake(string cakeNo, DateTime bookedDate)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();

            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("UPDATE Cake SET CashierDelivered = {0}, DeliveredDate = GETDATE() WHERE CakeNo = '{1}' AND DATEDIFF(day, '{2}', BookedDate) = 0", Storage.LoginUser, cakeNo, bookedDate.ToString("MM-dd-yyyy")), connection))
                {
                    int result = cmd.ExecuteNonQuery();
                    if (result == 1)
                    {
                        return true;
                    }
                }
                return false;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
        }

        internal static bool IsExistsBillNoAndBookingDate(string billNo, DateTime dateTime)
        {
            try
            {
                DataTable ds = new DataTable();

                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT BillNo FROM Cake WHERE BillNo = '{0}' AND DATEDIFF(YY, BookedDate, GETDATE()) = 0 ", billNo), connection))
                {
                    adapter.Fill(ds);
                    return ds.Rows.Count > 0;
                }
            }
            catch
            { }
            return true;
        }
    }

    /*internal static int TransferBillData()
    {
        TransactionScope scope = null;
        try
        {
            connection.Open();
            connectionMain.Open();
            TransactionOptions options = new TransactionOptions();
            options.Timeout = new TimeSpan(0, 3, 0);

            using (scope = new TransactionScope(TransactionScopeOption.Required, options))
            {
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT [BillNo],[DiscountByPercentage],[DiscountByCost],[TotalAmount],[TotalNetAmount],[CashPayment],[SodexhoPayment],[CardPayment],[IsBillCancelled],[Branch],[BillDate],[User],[CancelledReason],[IsSpecialBill],[DiscountReason] FROM [Bill]", connection);
                adapter.Fill(ds);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
       //             using (SqlCommand cmd = new SqlCommand("INSERT INTO [Bill] ([BillNo],[DiscountByPercentage],[DiscountByCost],[TotalAmount],[TotalNetAmount],[CashPayment],[SodexhoPayment],[CardPayment],[IsBillCancelled]
       //,[Branch]
       //,[BillDate]
       //,[User]
       //,[CancelledReason]
       //,[IsSpecialBill]
       //,[DiscountReason])
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {

                    }
                }



                //using (SqlCommand cmd1 = new SqlCommand("SELECT [BillNo],[DiscountByPercentage],[DiscountByCost],[TotalAmount],[TotalNetAmount],[CashPayment],[SodexhoPayment],[CardPayment],[IsBillCancelled],[Branch],[BillDate],[User],[CancelledReason],[IsSpecialBill],[DiscountReason] FROM [Bill]", connection))
                //{
                //    using (SqlCommand cmd2 = new SqlCommand(
                //}
                    
                    
                    
                    
                    
                    
                using (SqlCommand cmd = new SqlCommand("EXEC CentralTransferBill", connection))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        throw new Exception("");
                        //trans.Commit();
                        scope.Complete();
                        return Int32.Parse(result);
                    }
                }
            }
                

            //SqlTransaction trans = connection.BeginTransaction();
            //try
            //{
            //    using (SqlCommand cmd = new SqlCommand("EXEC CentralTransferBill", connection, trans))
            //    {
            //        string result = cmd.ExecuteNonQuery().ToString();
            //        if (result != null && result != string.Empty)
            //        {
            //            trans.Commit();
            //            return Int32.Parse(result);
            //        }
            //    }
            //    trans.Commit();
            return 1;
        }
        catch (Exception)
        {
            //trans.Rollback();
            return -1;
        }
        finally
        {
            connection.Close();
            scope.Dispose();
        }
    }*/

    /*internal static int TransferBillDetailData()
    {
        TransactionScope scope = null;

        try
        {
            connection.Open();
            TransactionOptions options = new TransactionOptions();
            options.Timeout = new TimeSpan(0, 3, 0);

            using (scope = new TransactionScope(TransactionScopeOption.Required, options))
            {
                using (SqlCommand cmd = new SqlCommand("EXEC CentralTransferBillDetail", connection))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        scope.Complete();
                        return Int32.Parse(result);
                    }
                }
            }

            //connection.Open();
            //SqlTransaction trans = connection.BeginTransaction();
            //try
            //{
            //    using (SqlCommand cmd = new SqlCommand("EXEC CentralTransferBillDetail", connection, trans))
            //    {
            //        string result = cmd.ExecuteNonQuery().ToString();
            //        if (result != null && result != string.Empty)
            //        {
            //            trans.Commit();
            //            return Int32.Parse(result);
            //        }
            //    }
            //    trans.Commit();
            return 1;
        }
        catch (Exception)
        {
            //trans.Rollback();
            return -1;
        }
        finally
        {
            connection.Close();
        }
    }*/

    public class Employee
    {
        public int EmpID { get; set; }
        public string EmpName { get; set; }
        public string Designation { get; set; }
        public string Department { get; set; }
        public string Branch { get; set; }
        public string EmpIDName { get; set; }
    }

    public class CakeShape
    {
        public int ID { get; set; }
        public string Shape { get; set; }
    }

    public class CakeFlavour
    {
        public int FlavourID { get; set; }
        public string Flavour { get; set; }
        public double PricePerKG { get; set; }
    }

    public class Branch
    {
        public int BranchID { get; set; }
        public string BranchName { get; set; }
    }

    public class Expense
    {
        public int ID { get; set; }
        public string ExpenseItem { get; set; }
    }

    public class Supplier
    {
        public int SupplierID { get; set; }
        public string SupplierName { get; set; }
    }

    //public class CakeDetails
    //{
    //    public string CakeBillNo { get; set; }
    //    public string CustomerName { get; set; }
    //    public string PhoneNo { get; set; }
    //    public string DeliveryDate { get; set; }
    //    public string Flavour { get; set; }
    //    public string ShapeOrAlbumModel { get; set; }
    //    public string TotalAmount { get; set; }
    //    public string Balance { get; set; }
    //}
}